package ws.utils.jdbc;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import ws.utils.collections.table.Column;
import ws.utils.collections.table.ITable;
import ws.utils.collections.table.Row;
import ws.utils.collections.table.Table;

/**
 * This class inserts and retrieves several data from the database.
 *
 * @author Wilson Santos, 36228 <wilson.santos@ua.pt>
 */
public class DBManager {

  /** Database connection. */
  private Connection dbcon;

  /**
   * Create a new instance.
   */
  public DBManager() throws SQLException {
    dbcon = DBConnection.getDBCon();
  }

  /**
   * Retrieves all tables from the database.
   *
   * @return list with the tables names.
   */
  public List<String> getDBTables() throws SQLException {
    List<String> list = new LinkedList<String>();

    DatabaseMetaData dbm = dbcon.getMetaData();
    ResultSet rs = dbm.getTables(null, null, "%", new String[]{"TABLE"});

    while (rs.next()) {
      list.add(rs.getString("TABLE_NAME"));
    }
    return list;
  }

  /**
   * Get the id of the last element added to some table.
   *
   * @return id of the last element added to some table.
   *
   * @throws SQLException
   */
  public Integer getIdentity() throws SQLException {
    Integer id = null;
    Statement stmt = null;
    String sql = null;

    /* Get the id of the created category. */
    sql = "SELECT @@IDENTITY AS 'Identity'";
    stmt = dbcon.createStatement();
    ResultSet rs = stmt.executeQuery(sql);

    if (rs.next()) {
      id = rs.getInt(1);
    }
    stmt.close();
    return id;
  }

  /**
   * Check if a table existsTable on the database.
   *
   * @param table table name.
   *
   * @return {@code true} if existsTable, otherwise {@code false}.
   *
   * @throws SQLException if database error occurs.
   */
  public boolean existsTable(String table) throws SQLException {
    String sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + table + "'";
    return exists(sql);
  }

  /**
   * Check if stored procedure exists on the database.
   *
   * @param spName stored procedure name.
   *
   * @return {@code true} if exists, otherwise {@code false}.
   *
   * @throws SQLException if database error occurs.
   */
  public boolean existsSP(String spName) throws SQLException {
    String sql = "SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + spName + "]') AND type in (N'P', N'PC')";
    return exists(sql);
  }

  /**
   * Check if user-defined function exists on the database.
   *
   * @param udfName user-defined function name.
   *
   * @return {@code true} if exists, otherwise {@code false}.
   *
   * @throws SQLException if database error occurs.
   */
  public boolean existsUDF(String udfName) throws SQLException {
    String sql = "SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + udfName + "]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')";
    return exists(sql);
  }

  private boolean exists(String sql) throws SQLException {
    Statement stmt = dbcon.createStatement();

    if (stmt.executeQuery(sql).next()) {
      return true;
    } else {
      return false;
    }
  }

  /**
   * Execute SQL code read from a file. For now, only DDL is supported.
   *
   * @param file file with SQL code.
   *
   * @return number of affected rows.
   *
   * @throws IOException
   * @throws SQLException
   */
  public boolean executeSQLScript(File file) throws IOException, SQLException {
    BufferedReader buffer = new BufferedReader(new FileReader(file));
    String sql = "";
    String line;
    while ((line = buffer.readLine()) != null) {
      sql = sql + '\n' + line;
    }
    Statement stmt = dbcon.createStatement();
    boolean ret = stmt.execute(sql);
    stmt.close();
    return ret;
  }

  /**
   * Get table contents from the database.
   *
   * @param tableName table name.
   *
   * @return table object with the data from the database.
   *
   * @throws SQLException
   */
  public ITable getTableContents(String tableName) throws SQLException {
    ResultSet rs = getResultSet("SELECT * FROM " + tableName);
    return resultSetToTable(rs);
  }

  public ResultSet getResultSet(String query) throws SQLException {
    Statement stmt = dbcon.createStatement();
    return stmt.executeQuery(query);
  }

  public ITable getTableContentsWhere(String tableName, String[] columns, String[] values) throws IllegalArgumentException, SQLException {
    // <editor-fold defaultstate="collapsed" desc="Argument validation">
    if (columns.length < 1) {
      throw new IllegalArgumentException("At least one column must be supplied for the WHERE clause");
    }
    if (columns.length != values.length) {
      throw new IllegalArgumentException("The size of the array 'columns' must match the size of the array 'values'");
    }// </editor-fold>

    String query = "SELECT * FROM " + tableName + " WHERE ";
    query += columns[0] + "=" + values[0];
    for (int i = 1; i < columns.length; ++i) {
      query += " AND " + columns[i] + "=" + values[i];
    }

    ResultSet rs = getResultSet(query);
    return resultSetToTable(rs);
  }

  public ITable getTableContentsLike(String tableName, String[] columns, String[] values) throws IllegalArgumentException, SQLException {
    // <editor-fold defaultstate="collapsed" desc="Argument validation">
    if (columns.length < 1) {
      throw new IllegalArgumentException("At least one column must be supplied for the WHERE clause");
    }
    if (columns.length != values.length) {
      throw new IllegalArgumentException("The size of the array 'columns' must match the size of the array 'values'");
    }// </editor-fold>

    String query = "SELECT * FROM " + tableName + " WHERE ";
    query += columns[0] + " LIKE " + values[0];
    for (int i = 1; i < columns.length; ++i) {
      query += " AND " + columns[i] + " LIKE " + values[i];
    }

    ResultSet rs = getResultSet(query);
    return resultSetToTable(rs);
  }

  private ITable resultSetToTable(ResultSet rs) throws SQLException {
    ITable table = new Table();

    ResultSetMetaData md = rs.getMetaData();
    int columnCount = md.getColumnCount();

    /* Fill table header. */
    Row headerRow = new Row();
    for (int i = 1; i <= columnCount; ++i) {
      // the column name and its contents are the same.
      headerRow.addColumn(new Column(md.getColumnName(i), md.getColumnName(i)));
    }
    table.setHeader(headerRow);

    /* Fill table rows. */
    while (rs.next()) {
      Row tableRow = new Row();
      for (int i = 1; i <= columnCount; ++i) {
        tableRow.addColumn(new Column(headerRow.getColumn(i - 1).getName(), rs.getObject(i).toString()));
      }
      table.addRow(tableRow);
    }

    return table;
  }

  /**
   * Clear the contents of one table. Attention, this will <b>erase all</b> its
   * contents, so use with caution.
   *
   * @param table table name.
   *
   * @return number of affected rows.
   *
   * @throws SQLException
   */
  public int clearTableContents(String table) throws SQLException {
    String sql = "DELETE FROM " + table;
    Statement stmt = dbcon.createStatement();
    int n = stmt.executeUpdate(sql);
    stmt.close();
    return n;
  }

  public int executeUpdate(String sql) throws SQLException {
    Statement stmt = dbcon.createStatement();
    int ret = stmt.executeUpdate(sql);
    stmt.close();
    return ret;
  }
}
